package cn.zzszxyy.hr.repository;

import cn.zzszxyy.hr.model.DeptTransferEntity;
import cn.zzszxyy.hr.viewmodel.DeptTransView;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 作者:张伟
 * <p>
 * 联系:adamzzww@163.com
 * 时间:2016-11-28
 * 说明:无
 */
@Repository
public interface DeptTransferRepository extends JpaRepository<DeptTransferEntity, Integer>, JpaSpecificationExecutor<DeptTransferEntity> {

    public final static String FIND_DeptTransView_BYDEPT_QUERY = "SELECT new cn.zzszxyy.hr.viewmodel.DeptTransView(c.id,c.transDate," +
            "(select e.employeeName from EmployeeEntity e where e.employeeId = c.employeeId) as employeeName," +
            "(select d.deptName from DepartmentEntity d where d.deptId = c.fromDept) as fromDeptName," +
            "(select d.deptName from DepartmentEntity d where d.deptId = c.toDept) as toDeptName," +
            "c.reason)" +
            "  FROM DeptTransferEntity c" +
            "  where c.fromDept = ?1 or c.toDept = ?1";

    public final static String FIND_DeptTransView_QUERY = "SELECT new cn.zzszxyy.hr.viewmodel.DeptTransView(c.id,c.transDate," +
            "(select e.employeeName from EmployeeEntity e where e.employeeId = c.employeeId) as employeeName," +
            "(select d.deptName from DepartmentEntity d where d.deptId = c.fromDept) as fromDeptName," +
            "(select d.deptName from DepartmentEntity d where d.deptId = c.toDept) as toDeptName," +
            "c.reason)" +
            "  FROM DeptTransferEntity c";

    public final static String FIND_DeptTransView_BYEMP_QUERY = "SELECT new cn.zzszxyy.hr.viewmodel.DeptTransView(c.id,c.transDate," +
            "(select e.employeeName from EmployeeEntity e where e.employeeId = c.employeeId) as employeeName," +
            "(select d.deptName from DepartmentEntity d where d.deptId = c.fromDept) as fromDeptName," +
            "(select d.deptName from DepartmentEntity d where d.deptId = c.toDept) as toDeptName," +
            "c.reason)" +
            "  FROM DeptTransferEntity c" +
            "  where c.employeeId = ?1";

    @Query(value = FIND_DeptTransView_BYDEPT_QUERY,
            countQuery = "SELECT count(s) FROM DeptTransferEntity s WHERE s.fromDept = ?1 or s.toDept = ?1")
    Page<DeptTransView> findDeptTransViewByDept(Integer deptId, Pageable pageable);

    @Query(value = FIND_DeptTransView_QUERY,
            countQuery = "SELECT count(s) FROM DeptTransferEntity s")
    Page<DeptTransView> findDeptTransView(Pageable pageable);

    @Query(value = FIND_DeptTransView_BYEMP_QUERY)
    List<DeptTransView> findByEmployeeId(Integer employeeId);

}
